package edu.zut.cs.network.sqlproject.teach.dao.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.mysql.jdbc.Statement;

import edu.zut.cs.network.sqlproject.teach.dao.StudentDao;
import edu.zut.cs.network.sqlproject.teach.entity.Student;

public class StudentDaoImpl implements StudentDao {
	protected Logger logger = LogManager.getLogger(this.getClass().getName());
	Connection conn;
	PreparedStatement st;
	ResultSet rs;
	private String driver;
	private String url;
	private String user;
	private String pass;
	List<Student> students = null;

	public StudentDaoImpl()  {
		try {
			this.initParam("mysql.ini");
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = DriverManager.getConnection(url, user, pass);
			st = conn.prepareStatement("INSERT INTO STU VALUE(NULL,?,?,?)");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void initParam(String paramfile) throws FileNotFoundException, IOException {
		Properties props = new Properties();
		props.load(new FileInputStream(paramfile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	public void create(Student student) {
		try {
			String sql = "INSERT INTO STU( ID , CLASS , CODE , FULLNAME ) VALUES(" + null + ",'"
					+ student.getClassAndGrades() + "','" + student.getCode() + "','" + student.getFullName() + "')";
			logger.debug(student);
			@SuppressWarnings("unused")
			int result = st.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void remove(String code) {
		try {
			String sql = "DELETE FROM STU WHERE CODE = " + code;
			logger.debug("刪除记录成功！");
			@SuppressWarnings("unused")
			int result = st.executeUpdate(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void update() throws SQLException {
		String change = "update stu.stu set fullname=? where id=?";
		PreparedStatement preparedStatement = conn.prepareStatement(change);

		String id, name;
		System.out.println("请输入需要修改名字的成员学号：");
		@SuppressWarnings("resource")
		Scanner num = new Scanner(System.in);
		id = num.next();

		System.out.println("请输入修改后的名字：");
		name = num.next();

		preparedStatement.setString(1, name);
		preparedStatement.setString(2, id);
		preparedStatement.execute();

		System.out.println("修改成功！...");

		@SuppressWarnings("unused")
		Statement statement = (Statement) conn.createStatement();
		ResultSet resultSet = preparedStatement.executeQuery("select * from stu.stu");
		while (resultSet.next()) {
			System.out.println(resultSet.getString("ID") + " " + resultSet.getString("CLASS") + " "
					+ resultSet.getString("CODE") + " " + resultSet.getString("FULLNAME"));
		}
		resultSet.close();
		preparedStatement.close();
		conn.close();

	}

	public Student findByName(String name) {
		Student student = null;
		try {
			String selectSQL = "SELECT ID, CLASS, CODE, FULLNAME FROM STU WHERE FULLNAME =  " + name;
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getLong("ID"));
				student.setClassAndGrades(rs.getString("CLASS"));
				student.setCode(rs.getString("CODE"));
				student.setFullName(rs.getString("FULLNAME"));
				if (student.getFullName() == name) {
					logger.info("found student:" + student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return student;
	}

	public Student findByCode(String code) {
		Student student = null;
		try {
			String selectSQL = "SELECT ID, CLASS, CODE, FULLNAME FROM STU WHERE CODE =  " + code;
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getLong("ID"));
				student.setClassAndGrades(rs.getString("CLASS"));
				student.setCode(rs.getString("CODE"));
				student.setFullName(rs.getString("FULLNAME"));
				if (student.getCode() == code) {
					logger.info("found student:" + student);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return student;
	}

	public Student findById(Long id) {
		// TODO Auto-generated method stub
		Student student = null;
		try {
			String selectSQL = "SELECT ID , CLASS , CODE , FULLNAME FROM STU WHERE ID = " + id;
			logger.debug(selectSQL);
			logger.debug('\n');
			ResultSet rs = this.st.executeQuery(selectSQL);
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getLong("ID"));
				student.setClassAndGrades(rs.getString("CLASS"));
				student.setCode(rs.getString("CODE"));
				student.setFullName(rs.getString("FULLNAME"));
				if (student.getId().equals(id)) {
					logger.info("found student:" + student + "by code\n");
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return student;
	}

}
